/*
Build county-level IVs

[] brackets indicate redacted internal census variable name or directory that can't be disclosed. 


Need: 
Mean wind speed by county (cnty_meanspeed)
national trend data (hexpanel2)
total wind capacity by county by year (countylevelwindwrkrs)
predicted wind capacity by county by year using Stata makeLinearIVs.do-type program
aggregate to the rings using nearby_wind_IVs.sas or WorkersCounties.sas or EstabsCounties.sas-type program
*/

* define libraries;
libname icf '[directory location for individual characteristics files]';
libname hannah  '/projects/users/########';
libname ben '/projects/users/########';

* GET COUNTY BOUNDARY SHAPEFILES;
data countyboundaries;
  set mapsgfk.us_counties 
  (where=(state in (01 04 05 06 08 10 11 17 18 19 20 23 24 29 30 31 32 35 38 40 47 51 56)));
  x = long;
  y = lat;
  ctyfips = substr(ID,4,5);
run;
data countyboundaries;
  set countyboundaries (keep = ID ctyfips SEGMENT X Y);
run;

data work.hexpanel2;
  set hannah.hexpanel2;
  rename xlong_center = x;
  rename ylat_center = y;
run;
* ASSIGN COUNTY FIPS TO EACH HEXAGON;
PROC GINSIDE 
  DATA= work.hexpanel2
  MAP = countyboundaries
  OUT = hannah.county_ivs;
  ID id;
RUN;
data hannah.county_ivs;
  set hannah.county_ivs (drop=SEGMENT VAR1);
  IF CMISS(ID) THEN DELETE;
run;
data hannah.county_ivs;
  set hannah.county_ivs;
  ctyfips = substr(ID,4,5);
run;

/* aggregate to county level */
proc sql;
  create table hannah.cnty_level_ivs as 
  select ctyfips, 
  Year as year,
  mean(wspeed_100m_mpersec) as meanspd,
  mean(USturbcap_new) as USturbcap_new,
  mean(USturbnum_new) as USturbnum_new,
  mean(USplntnum_new) as USplntnum_new,
  mean(USturbcap) as USturbcap,
  mean(USturbnum) as USturbnum,
  mean(USplntnum) as USplntnum,
  sum(t_cap_s) as tot_t_cap_s,
  sum(t_cap_l) as tot_t_cap_l,
  sum(cond_exp_ycap) as cond_exp_ycap,
  sum(cond_exp_ytnum) as cond_exp_ytnum
  from hannah.county_ivs
  group by ctyfips, Year
  order by ctyfips, Year;
quit;

* merge in county-level capacity by year;
data work.countycaps;
  set hannah.countylevelwindwrkrs
  (keep = ctyfips year c_tcap);
run;

proc sql;
  create table hannah.cnty_level_ivs as
  select 
    a.*, b.c_tcap
  from hannah.cnty_level_ivs as a
    left join work.countycaps as b on a.ctyfips = b.ctyfips and a.year = b.year
  order by a.ctyfips, a.year;
quit;
